package com.base.controller;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.base.comm.CommContent;
import com.base.comm.CommExcel;
import com.base.comm.CommUrls;
import com.base.po.voip.Cards;
import com.base.po.voip.Rechargeablecards;
import com.base.service.CardsService;
import com.base.service.RechargeablecardsService;

@Controller
@RequestMapping("/admin")
public class daoruka {
	@Autowired
	private CardsService cardsService;
	@Autowired
	private RechargeablecardsService rechargeablecardsService;
	
	/**
     * 读取Excel文件
     * @param file 
     * @return
     * @throws IOException
     */
    public void readExcel(HttpServletRequest request,HttpServletResponse response,String file) throws Exception{
		
		PrintWriter out = response.getWriter();
        if (file == null || CommExcel.EMPTY.equals(file)) {
            out.println("请传入正确的Excel文件");
            return;
        } else {
            String postfix = getPostfix(file);
            if (!CommExcel.EMPTY.equals(postfix)) {
                if (CommExcel.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                  System.out.println(file);
                  readXls(file);
                } else if (CommExcel.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                	System.out.println(file);
                    readXlsx(file);
                }
            } else {
                System.out.println(file + CommExcel.NOT_EXCEL_FILE);
            }
        }
    }
	
    /**
     * 读取 Excel 2010
     * @param file
     * @return
     * @throws IOException
     */
    public void readXlsx(String file) throws Exception {
        System.out.println(CommExcel.PROCESSING + file);
        InputStream is = new FileInputStream(file);
        @SuppressWarnings("resource")
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Cards cards = null;
        // 读取表格
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
               continue;
            }
            // 读取行
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	cards = new Cards();
                	DecimalFormat df = new DecimalFormat("#");
                	Double cardno = xssfRow.getCell(0).getNumericCellValue();
                	Double pwd = xssfRow.getCell(1).getNumericCellValue();
                    
                    cards.setCardno(df.format(cardno));
                    cards.setPwd(df.format(pwd));
                    
                    CommExcel.cardsList.add(cards);
                }
            }
        }
    }
    
    /**
     * 读取 Excel 2003-2007
      * @param file
      * @return
      * @throws IOException
      * @throws ParseException 
      */
    public void readXls(String file) throws IOException, ParseException {
        System.out.println(CommExcel.PROCESSING + file);
        InputStream is = new FileInputStream(file);
        @SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        Cards cards = null;
        // 读取表格
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 读取行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	cards = new Cards();
                	DecimalFormat df = new DecimalFormat("#");
                	Double cardno = hssfRow.getCell(0).getNumericCellValue();
                	Double pwd = hssfRow.getCell(1).getNumericCellValue();
                    
                    cards.setCardno(df.format(cardno));
                    cards.setPwd(df.format(pwd));
                    
                    CommExcel.cardsList.add(cards);
                }
            }
        }
    }
    
    //Excal 2007-2010 取值
    @SuppressWarnings({ "static-access", "deprecation" })
    private String getXValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
    
   //Excal 2003 取值
    @SuppressWarnings({ "static-access", "deprecation" })
    private String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
           return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

 
    //判断Excel 2003 或者2007-2010
    public static String getPostfix(String file) {
    	
        if (file == null || CommExcel.EMPTY.equals(file)) {
            return CommExcel.EMPTY;
        }
        
        if (file.contains(CommExcel.POINT)) {
           return file.substring(file.lastIndexOf(CommExcel.POINT) + 1);
        }
        return CommExcel.EMPTY;
    }
    
    
    //跳转到确认订单页面
    @RequestMapping("/content_daoruka")
    private ModelAndView index(HttpServletRequest request,HttpServletResponse response,String file) throws Exception {
    	response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=UTF-8");
    	ModelAndView modelAndView =new ModelAndView();
    	
    	System.out.println(file);
    	readExcel(request,response,file);
    	System.out.println("进入");
    	
    	System.out.println("卡数"+CommExcel.cardsList.size());
    	
    	insertCards(request, response);
    	
    	modelAndView.setViewName("jsp/daoruka");
    	return modelAndView;
    	
	}
    
    //核对查看后返回
    @RequestMapping("/insertCards")
    private void insertCards(HttpServletRequest request, HttpServletResponse response) throws IOException {
    	response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=UTF-8");
    	PrintWriter out = response.getWriter();
    	
    	System.out.println("开始");

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(CommUrls.JDBC, CommUrls.JDBC_USER, CommUrls.JDBC_PASSWORD);
			String sql = " insert into cards ( CardNo, Pwd ) values (?,?)";
			pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			conn.setAutoCommit(false);
			
			for (int i = 1; i <= CommExcel.cardsList.size(); i++) {
				pstm.setString(1 , CommExcel.cardsList.get(i-1).getCardno());
				pstm.setString(2, CommExcel.cardsList.get(i-1).getPwd());
				pstm.addBatch();
				if ((i + 1) % 1000 == 0) {
					System.out.println("导入第"+i+"张");
					pstm.executeBatch();// 每执行一次就清一次sql缓存。
					pstm.clearBatch(); // 否则每一次是执行1000，2000，3000...条sql语句而不是每一次执行1000条
				}
			}
			pstm.executeBatch();
			conn.commit();
			System.out.println("导入结束");
		} catch (Exception e) {
			out.print("导入失败，请您重试");
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException(e);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					out.print("导入失败，请您重试");
					e.printStackTrace();
					throw new RuntimeException(e);
				}
			}
		}
		
		CommExcel.cardsList.clear();
	}
    
    @RequestMapping(value = "/updateCardsPwd", method = RequestMethod.POST)
	public void updateCards(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// 返回参数
		response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		System.out.println("进入");
		List<Cards> cards = cardsService.selectIDAndPwd();
		
		String result = "更新密码的卡号:";
		
		for(Cards c : cards) {
			if(!c.getRechargeablecardspwd().equals(c.getPwd())) {
				Rechargeablecards rechargeablecards = rechargeablecardsService.selectByPrimaryKey(c.getRechargeablecardsid());
				System.out.println(rechargeablecards.getCardno());
				rechargeablecards.setPwd(c.getPwd());
				rechargeablecardsService.updateByPrimaryKeySelective(rechargeablecards);
				System.out.println("更新密码的卡号"+rechargeablecards.getCardno());
				result += rechargeablecards.getCardno()+",";
			}
		}
		
		out.print("修改完成,"+result);
	}
       
}
